package util;

import user.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DBUtilDao { //执行增删改查
    JDBCUtils jdbcUtils = new JDBCUtils();
    ResultSet resultSet = null;
    PreparedStatement preparedStatement = null;
    Connection connection = null;

    {
        try {
            connection = jdbcUtils.getConnection();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public void addOne(Register register) throws SQLException { //在数据库中注册用户
        String sql = "insert into register (phoneNumber,password) values(?,?)";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, register.getPhoneNumber());
        preparedStatement.setString(2, register.getPassword());
        preparedStatement.executeUpdate();
        addMember(register);//添加member2表
    }

    public void addMember(Register register) throws SQLException {
        String sql = "insert into member2 (phoneNumber) values (?)";//注册用户之后，初始化个人中心的积分，余额，优惠券
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, register.getPhoneNumber());
        preparedStatement.executeUpdate();
    }

    public Member getMember(String phoneNumber) throws SQLException {//查询个人中心表“member2”返回一个Member对象
        Member member = null;
//        String sql = String.format("select * from member2 where phoneNumber='%s'", phoneNumber);//查询个人中心表的信息
        //上一行代码存在SQL注入的安全性问题
        String sql = "select * from member2 where phoneNumber=?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, phoneNumber);
        resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            member = new Member(resultSet.getString("phoneNumber"), resultSet.getFloat("integral"), resultSet.getFloat("balance"), resultSet.getInt("coupon"));
        }
        return member;
    }

    public void updateMember(Member member) throws SQLException {
//        String sql = String.format("update member2 set integral='%f',balance='%f' where phoneNumber='%s'", member.getIntegral(), member.getBalance(), member.getPhoneNumber());
        String sql = "update member2 set integral=?,balance=? where phoneNumber=?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setFloat(1, member.getIntegral());
        preparedStatement.setFloat(2, member.getBalance());
        preparedStatement.setString(3, member.getPhoneNumber());
        preparedStatement.executeUpdate();
    }

    public Register getOne(Register register) throws SQLException { //根据传递进来的用户信息，查询数据库，返回用户名和密码
        Register register1 = null;
        //       String sql = String.format("select * from register where phoneNumber='%s'", register.getPhoneNumber());
        String sql = "select * from register where phoneNumber=?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, register.getPhoneNumber());
        resultSet = preparedStatement.executeQuery();
        while (resultSet != null && resultSet.next()) {
            register1 = new Register(resultSet.getString("phoneNumber"), resultSet.getString("password"));
        }
        return register1;
    }

    public List<Register> getRegister(String username) throws SQLException { //对register表的查询操作
//        String sql = String.format("select * from register where phoneNumber='%s'", username);
        String sql = "select * from register where phoneNumber=?";
        List<Register> list = new ArrayList<>();
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, username);
        resultSet = preparedStatement.executeQuery();
        while (resultSet != null && resultSet.next()) {
            Register register = new Register(resultSet.getString("phoneNumber"), resultSet.getString("password"));
            list.add(register);
        }
        return list;
    }

    public void updateShopCar(ShopCar shopCar) throws SQLException { //添加购物车
        //第1步，先判断商品的名字在数据库中存不存在
        List<ShopCar> shopCars = getShopCar(shopCar);//按商品名字查找数据库
        String sql;
        if (shopCars.size() == 0) { //当前商品不存在
            sql = "insert into shopCar (phoneNumber,number,name) values(?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, shopCar.getPhoneNumber());
            preparedStatement.setInt(2, shopCar.getNumber());
            preparedStatement.setString(3, shopCar.getName());
        } else {//商品已经存在
//            sql = String.format("update shopCar set number=number+1 where phoneNumber='%s' and name='%s'", shopCar.getPhoneNumber(), shopCar.getName());
            sql = "update shopCar set number=number+1 where phoneNumber=? and name=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, shopCar.getPhoneNumber());
            preparedStatement.setString(2, shopCar.getName());
        }
        preparedStatement.executeUpdate();

    }

    public void cleanShopCar(ShopCar shopCar) throws SQLException { //清空用户购物车
        List<ShopCar> shopCars = getShopCar(shopCar.getPhoneNumber());
        if (shopCars.size() == 0) { //当前商品不存在
        } else {//商品已经存在
//            String sql = String.format("delete from shopCar where phoneNumber='%s'", shopCar.getPhoneNumber());
            String sql = "delete from shopCar where phoneNumber=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, shopCar.getPhoneNumber());
            preparedStatement.executeUpdate();
        }

    }

    public void deleteShopCar(ShopCar shopCar) throws SQLException { //删除购物车中的指定商品
        List<ShopCar> shopCars = getShopCar(shopCar.getPhoneNumber());
        if (shopCars.size() == 0) { //当前商品不存在
        } else {//商品已经存在
//            String sql = String.format("delete from shopCar where phoneNumber='%s'and name='%s'", shopCar.getPhoneNumber(), shopCar.getName());
            String sql = "delete from shopCar where phoneNumber=? and name=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, shopCar.getPhoneNumber());
            preparedStatement.setString(2, shopCar.getName());
            preparedStatement.executeUpdate();
        }
    }

    public void subUpdateShopCar(ShopCar shopCar) throws SQLException {//减少购物车
        //第1步，先判断商品的名字在数据库中存不存在
        List<ShopCar> shopCars = getShopCar(shopCar);//按商品名字查找数据库
        String sql;
        if (shopCars.size() > 0) { //当前商品存在
            //           sql = String.format("update shopCar set number=number-1 where phoneNumber='%s' and name='%s'", shopCar.getPhoneNumber(), shopCar.getName());
            sql = "update shopCar set number=number-1 where phoneNumber=? and name=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, shopCar.getPhoneNumber());
            preparedStatement.setString(2, shopCar.getName());
        }
        preparedStatement.executeUpdate();
    }

    public List<ShopCar> getShopCar(ShopCar shopCar) throws SQLException { //按商品名字查找购物车
        List<ShopCar> list = new ArrayList<>();
//        String sql = String.format("select phoneNumber,number,shopCar.name,price from shopCar,fruit where shopCar.name=fruit.name and phoneNumber='%s'and shopCar.name='%s'", shopCar.getPhoneNumber(), shopCar.getName());//查询购物车数据表
        String sql = "select phoneNumber,number,shopCar.name,price from shopCar,fruit where shopCar.name=fruit.name and phoneNumber=? and shopCar.name=?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, shopCar.getPhoneNumber());
        preparedStatement.setString(2, shopCar.getName());
        resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            ShopCar shopCar2 = new ShopCar(resultSet.getString("phoneNumber"), resultSet.getInt("number"), resultSet.getFloat("price"), resultSet.getString("name"));
            list.add(shopCar2); //把查询结果放入list集合
        }
        return list;
    }

    public List<ShopCar> getShopCar(String phoneNumber) throws SQLException { //查询购物车信息，返回一个list集合
        List<ShopCar> list = new ArrayList<>();
//        String sql = String.format("select phoneNumber,number,shopCar.name,price from shopCar,fruit where shopCar.name=fruit.name and phoneNumber='%s'", phoneNumber);//查询购物车数据表
        String sql = "select phoneNumber,number,shopCar.name,price from shopCar,fruit where shopCar.name=fruit.name and phoneNumber=?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, phoneNumber);
        resultSet = preparedStatement.executeQuery();
        while (resultSet != null && resultSet.next()) {
            ShopCar shopCar = new ShopCar(resultSet.getString("phoneNumber"), resultSet.getInt("number"), resultSet.getFloat("price"), resultSet.getString("name"));
            list.add(shopCar); //把查询结果放入list集合
        }
        return list;
    }

    public List<Fruit> getFruit() throws SQLException {
        List<Fruit> list = new ArrayList<>();
        String sql = "select * from fruit";
        preparedStatement = connection.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        while (resultSet != null && resultSet.next()) {
            Fruit fruit = new Fruit(resultSet.getString("name"), resultSet.getFloat("price"));
            list.add(fruit);
        }
        return list;
    }

    public List<Order> getOrder(String phoneNumber, int flag) throws SQLException { //获取订单信息
        List<Order> list = new ArrayList<>();
        //       String sql = String.format("select * from order2 where phoneNumber='%s'and flag='%s'", phoneNumber, flag);//查询购物车数据表
        String sql = "select * from order2 where phoneNumber=? and flag=?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, phoneNumber);
        preparedStatement.setInt(2, flag);
        resultSet = preparedStatement.executeQuery();
        while (resultSet != null && resultSet.next()) {
            Order order1 = new Order(resultSet.getString("phoneNumber"), resultSet.getInt("number"), resultSet.getFloat("price"), resultSet.getString("name"), resultSet.getInt("flag"));
            list.add(order1);
        }
        return list;
    }

    public void updateOrder(String phoneNumber, int flag) throws SQLException {
        //       String sql = String.format("update order2 set flag='%d' where phoneNumber='%s'", flag, phoneNumber); //设置flag的值，0表示未完成订单，1表示已完成订单
        String sql = "update order2 set flag=? where phoneNumber=?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, flag);
        preparedStatement.setString(2, phoneNumber);
        preparedStatement.executeUpdate();
    }

    public void addOrder(ShopCar shopCar) throws SQLException {//更新订单信息，添加
        String sql = "insert into order2 (phoneNumber,number,price,name,flag) values(?,?,?,?,'0')";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, shopCar.getPhoneNumber());
        preparedStatement.setInt(2, shopCar.getNumber());
        preparedStatement.setFloat(3, shopCar.getPrice());
        preparedStatement.setString(4, shopCar.getName());
        preparedStatement.executeUpdate();
    }

    public void addMoney(String phoneNumber, float price) throws SQLException {
        //      String sql = String.format("update member2 set balance=balance+'%f'  where phoneNumber='%s'", price, phoneNumber);
        String sql = "update member2 set balance=balance+?  where phoneNumber=?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setFloat(1, price);
        preparedStatement.setString(2, phoneNumber);
        preparedStatement.executeUpdate();
    }

    public void close() throws SQLException {
        if (connection != null) {
            connection.close();
        }
        if (preparedStatement != null) {
            preparedStatement.close();
        }
        if (resultSet != null) {
            resultSet.close();
        }
    }

}
